package com.groot.medical.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.groot.common.util.MedicalDBUtil;

import com.groot.medical.entity.ChronicPolicy;

public class ChronicPolicyDAOImpl implements ChronicPolicyDAO {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	@Override
	public int getAllChronicPolicyInfoCounts() {
		int count = 0;
        //sql拼接
		StringBuffer sql = new StringBuffer("SELECT COUNT(1) AS id FROM t_chronic_policy where 1=1");
        //查询结果
        try {
        	 conn = MedicalDBUtil.getConnection();
             ps = conn.prepareStatement(sql.toString());
             rs = ps.executeQuery();
            if(rs.next()) {
                count = rs.getInt("id");
            }
        } catch (SQLException e) {

    		e.printStackTrace();
    		}
    		finally{
    			MedicalDBUtil.closeAll(conn, ps, rs);
    		}
        return count;
	}

	@Override
	public List<ChronicPolicy> findChronicPolicyInfoByPage(int startIndex, int pageSize) {
		 StringBuffer sql = new StringBuffer("select * from t_chronic_policy where 1=1");
			
			
	        sql.append(" limit ?,? " );
	        try {
				conn = MedicalDBUtil.getConnection();
				ps = conn.prepareStatement(sql.toString());
				ps.setInt(1, startIndex);
				ps.setInt(2, pageSize);
				rs = ps.executeQuery();
				List<ChronicPolicy> policys = new ArrayList<>();
				
				while(rs.next()){
					ChronicPolicy policy= new ChronicPolicy();
					policy.setId(rs.getInt(1));
					policy.setYear(rs.getString(2));
					policy.setMoney(rs.getString(3));
					policy.setScale(rs.getFloat(4));
					policys.add(policy);	
				}
				
				return policys;
				
			} catch (SQLException e) {
				e.printStackTrace();
				
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
			return null;
	}

	@Override
	public void addChronicPolicyInfo(ChronicPolicy policy) {
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			String sql="insert into t_chronic_policy(year,money,scale)values(?,?,?)";
			ps=conn.prepareStatement(sql);
			ps.setString(1,policy.getYear());
			ps.setString(2,policy.getMoney());
			ps.setFloat(3, policy.getScale());
			
			ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
		
	}

	@Override
	public ChronicPolicy findChronicPolicyInfoById(String id) {
		
		 StringBuffer sql = new StringBuffer("select * from t_chronic_policy where id = ? ");
	        try {
				conn = MedicalDBUtil.getConnection();
				ps = conn.prepareStatement(sql.toString());
				ps.setString(1, id);
				rs = ps.executeQuery();
				ChronicPolicy policy=null;
				if(rs.next()){
					policy= new ChronicPolicy();
					policy.setId(rs.getInt(1));
					policy.setYear(rs.getString(2));
					policy.setMoney(rs.getString(3));
					policy.setScale(rs.getFloat(4));
						
				}
				
				return policy;
				
			} catch (SQLException e) {
				e.printStackTrace();
				
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
			return null;
	}

	@Override
	public void editChronicPolicyInfo(ChronicPolicy policy) {
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			//UPDATE student SET name='"+name+"', age=" + age+" where name='大红'";
			String sql="update t_chronic_policy set year=?,money=? ,scale=? where id=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1, policy.getYear());
			ps.setString(2,policy.getMoney());
			ps.setFloat(3,policy.getScale());
			ps.setInt(4,policy.getId());
			ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
		
	}

	@Override
	public ChronicPolicy findChronicPolicyInfoByYear(String nowTime) {
		StringBuffer sql = new StringBuffer("select * from t_chronic_policy where year = ? ");
        try {
			conn = MedicalDBUtil.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, nowTime);
			rs = ps.executeQuery();
			ChronicPolicy policy=null;
			if(rs.next()){
				policy= new ChronicPolicy();
				policy.setId(rs.getInt(1));
				policy.setYear(rs.getString(2));
				policy.setMoney(rs.getString(3));
				policy.setScale(rs.getFloat(4));
					
			}
			
			return policy;
			
		} catch (SQLException e) {
			e.printStackTrace();
			
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return null;
	}

}
